<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="LAFDC.DataAccess" %>
<%@ Import Namespace="LAFDC.Logging" %>
<script language="c#" runat="server">
	public static readonly string source = "ajax-save-json-array.aspx";
	protected void Page_Load(object sender, EventArgs e)
	{
		if (!Page.IsPostBack) {
			if (this.Request.QueryString.Count > 0) {
				string[] data = {};
				string array_type = this.Request.QueryString.Get("type");
				string json_array = this.Request.QueryString.Get("array");
				
				if (array_type == string.Empty || json_array == string.Empty)
					return;
					
				string [] in_data = getArrayfromJSON(json_array);
				
				switch (array_type) {
					case "asso":
						insert_asso(in_data);
					break;
					case "section":
						insert_section(in_data);
					break;
					case "addr":
						insert_addr(in_data);
					break;
					default:
						Response.Write("ERROR");
					break;
				}
			}
		}
	}
	
	private void insert_asso(string [] data)
	{
		string sql_query = "insert asso (ad_id, [AD Name]) values ('" + data[0] + "', '" + data[1] + "');";
		int result = ExecuteNonQuery(sql_query);
		if (result == -1)
			Response.Write("ERROR");
		else
			Response.Write("SUCCESS");
	}
	
	private void insert_section(string [] data)
	{
		string SectionId = "S-" + data[0] + "-" + data[1];
		string sql_query = "insert section (ad_id, [Section_Name], [section id]) values ('" + data[0] + "', '" + data[1] + "', '" + SectionId + "');";
		int result = ExecuteNonQuery(sql_query);
		if (result == -1)
			Response.Write("ERROR");
		else
			Response.Write("SUCCESS");
	}
	
	private void insert_addr(string [] data)
	{
		/* Check whether twe should update or insert */
		string check = "select 1 from adresse where prev_id = '" + data[0] + "'";

		if ( EntryNeedsUpdate(check)) {
			string update = "UPDATE Adresse "
					+ " set adresse_1 = '"+ data[1]
					+ "', adresse_2 = '" + data[2] 
					+ "', CodePostal = '" + data[3] 
					+ "', Ville = '" + data[4] 
					+ "', last_modified = getdate()"
					+ "WHERE PREV_ID ='" + data[0] + "'";
			int result = ExecuteNonQuery(update);
			if (result == -1) {
				Response.Write("ERROR");
			} else {
				Response.Write("SUCCESS");
			}
		} else {
			string insert = "INSERT Adresse ([prev_id],[adresse_1],[adresse_2],[CodePostal],[Ville],[Pays],[created],[last_modified])"
					+ "VALUES ('" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "', 'FRANCE',getdate(),getdate())";
			int result = ExecuteNonQuery(insert);
			if (result == -1) {
				Response.Write("ERROR");
			} else {
				Response.Write("SUCCESS");
			}
		}
	}
	
	private string [] getArrayfromJSON(string json_array)
	{
		string [] data = new string [20];
		int i = 0;
		string entry = string.Empty;
		
		foreach (char c in json_array.ToCharArray()) {
			if (c == '[') {
				continue;
			} else if (c == ',') {
				entry = entry.Trim("\"".ToCharArray());
				data[i++] = entry;
				entry = string.Empty;
			} else if (c == ']') {
				entry = entry.Trim("\"".ToCharArray());
				data[i] = entry;
				break;
			} else {
				entry += c.ToString();
			}
		}
		return data;
	}

	private int ExecuteNonQuery(string sql)
	{	
		ConnectionManager cn = new ConnectionManager();
		SqlConnection conn = cn.GetSqlConnection();
		SqlCommand tsql = conn.CreateCommand();

		tsql.CommandType = CommandType.Text;
		tsql.CommandText = sql;

		FDCLogger.LogEvent("Preparing to execute SQL statement: " + sql, source, 4);

		conn.Open();
		try {
			return tsql.ExecuteNonQuery();
		} 
		catch (Exception e) {
			FDCLogger.LogEvent(e.Message, source, 1);
			return -1;
		}
	}
	
	private bool EntryNeedsUpdate (string sql_check)
	{
		int cr = 0; //Check result

		ConnectionManager cn = new ConnectionManager();
		SqlConnection conn = cn.GetSqlConnection();
		SqlCommand tsql = conn.CreateCommand();

		tsql.CommandType = CommandType.Text;
		tsql.CommandText = sql_check;

		FDCLogger.LogEvent("Preparing to execute SQL statement: " + sql_check, source, 4);

		conn.Open();
		using (SqlDataReader reader = tsql.ExecuteReader()) {
			while (reader.Read()) {
				if (reader.HasRows) {
					cr++;
				}
			}
		}
		FDCLogger.LogEvent("SQL check returned " + cr.ToString() , source, 4);
		if (cr > 0)
			return true;
		else
			return false;
	}
</script>
